/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

/**
 * @author Administrador
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.DiaSemana;
import com.javabeans.Horario;
import com.javabeans.PeriodoDia;

public class AdminHorario implements HorarioManager{
    private Connection c;

    public AdminHorario(Connection c){
        this.c = c;
    }
    
    public void saveHorarios(List<Horario> horarios) throws DataException{
        ResultSet rs = null;
        PreparedStatement preSt = null;
        int i = 0;
        String query = "INSERT INTO HORARIO VALUES";
        try{
            for(Horario h:horarios){
                query += "("+h.getIdFacultad()+","+h.getIdPeriodoDia()+","+h.getIdDiaSemana()+","+h.isActivo()+"),";
                i++;
            }
            if(i > 0){
                i = query.lastIndexOf(",");
                query = query.substring(0, i);
            }            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminHorario -> saveHorarios(List<Horario> horarios) "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void deletePorPeriodo(int idFacultad,int idPeriodoDia) throws DataException{
        PreparedStatement preSt = null;
        String query = "DELETE FROM HORARIO WHERE IDFACULTAD="+idFacultad+" AND IDPERIODO_DIA="+idPeriodoDia;
        try{            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminHorario -> deletePorPeriodo(int idPeriodoDia) "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<Horario> getHorariosBasic(int idFacultad) throws DataException{
        List<Horario> horarios = new ArrayList<Horario>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDDIA_SEMANA";
            query += ", IDPERIODO_DIA";
            query += " FROM HORARIO";
            query += " WHERE IDFACULTAD="+idFacultad+" AND ACTIVO = 1";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Horario h = new Horario();
                h.setIdFacultad(idFacultad);
                h.setIdDiaSemana(rs.getInt("IDDIA_SEMANA"));
                h.setIdPeriodoDia(rs.getInt("IDPERIODO_DIA"));
                horarios.add(h);
            }
        }catch(SQLException sql){
            throw new DataException(" AdminHorario -> getHorariosBasic(int idFacultad) sql = "+sql.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return horarios;
    }
    
    @Override
    public List<Horario> getHorarios(int idFacultad) throws DataException{
        List<Horario> horarios = new ArrayList<Horario>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT H.IDDIA_SEMANA";
            query += ", H.IDPERIODO_DIA";
            query += ", DATE_FORMAT(PD.HORAINI,'%H:%i') HORAINI";
            query += ", DATE_FORMAT(PD.HORAFIN,'%H:%i') HORAFIN";
            query += ", DS.NOMBRE";
            query += " FROM HORARIO H";
            query += " INNER JOIN PERIODO_DIA PD ON H.IDFACULTAD=PD.IDFACULTAD AND H.IDPERIODO_DIA=PD.IDPERIODO_DIA";
            query += " INNER JOIN DIA_SEMANA DS ON H.IDDIA_SEMANA=DS.IDDIA_SEMANA";
            query += " WHERE H.IDFACULTAD="+idFacultad+" AND H.ACTIVO = 1";
            query += " ORDER BY DS.ORDEN";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Horario h = new Horario();
                h.setIdFacultad(idFacultad);
                h.setIdDiaSemana(rs.getInt("H.IDDIA_SEMANA"));
                h.setIdPeriodoDia(rs.getInt("H.IDPERIODO_DIA"));
                DiaSemana ds = new DiaSemana();
                ds.setNombre(rs.getString("DS.NOMBRE"));
                PeriodoDia pd = new PeriodoDia();
                pd.setHoraIni(rs.getString("HORAINI"));
                pd.setHoraFin(rs.getString("HORAFIN"));
                h.setPeriodoDia(pd);
                h.setDiaSemana(ds);
                horarios.add(h);
            }
        }catch(SQLException sql){
            throw new DataException(" AdminHorario -> getHorarios(int idFacultad) sql = "+sql.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return horarios;
    }    
}
